﻿/*********************************************************************************************************************
VanguardDocumentation, Procedure GetTextProperty

© 2014 Erik Ackerholm

This file is part of SqlVanguard.

SqlVanguard is free software: you can redistribute it and/or modify it under the terms of the GNU General Public 
License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later 
version.

SqlVanguard is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied 
warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with SqlVanguard. If not, see 
<http://www.gnu.org/licenses/>.
*********************************************************************************************************************/

if
	exists (
		select *
		from sys.schemas as s
			inner join sys.objects as o on (s.schema_id = o.schema_id)
		where s.name = 'VanguardDocumentation' and o.[type] = 'FN'
			and o.name = 'GetTextProperty'
	)
begin
	drop function VanguardDocumentation.GetTextProperty;
end;
GO

if
	not exists (
		select *
		from sys.schemas as s
			inner join sys.objects as o on (s.schema_id = o.schema_id)
		where s.name = 'VanguardDocumentation' and o.[type] = 'P'
			and o.name = 'GetTextProperty'
	)
begin
	declare @SqlCommand as nvarchar(max);
	set @SqlCommand = '
		create procedure VanguardDocumentation.GetTextProperty
		as raiserror (''Procedure GetTextProperty is not implemented yet!'', 11, 1);
	';
	execute (@SqlCommand);
end;
GO

alter procedure VanguardDocumentation.GetTextProperty
	/*****************************************************************************************************************
	The inverse of SetTextProperty - Fetches text from one or more extended properties for an entity.
	
	Returns null if no property is found.
	*****************************************************************************************************************/
	
	@EntityName                 nvarchar(512),
		/* Dot-separated name of entity where the text is stored. */
	@PropertyName               nvarchar(64),
		/* Name of property. This will include indexed properties, i.e. names like 'MyProp_1', 'MyProp_2', ... */
	@Text                       nvarchar(max)                       output,
		/* Concenated text from the extended properties. */
	@EntityType                 varchar(128)                = null
		/* Optionally give the type of entity where the text is stored. */
as
begin
	
	set nocount on;
	set xact_abort on;
	set lock_timeout 3000;
	set transaction isolation level read committed;
	
	declare @ErrorNo as integer;
	declare @ErrorMsg as nvarchar(1536);
	declare @ErrorLine as integer;
	declare @ErrorState as integer;
	
	declare @Level0Name as nvarchar(128);
	declare @Level1Name as nvarchar(128);
	declare @Level2Name as nvarchar(128);
	
	declare @Level0Type as varchar(64);
	declare @Level1Type as varchar(64);
	declare @Level2Type as varchar(64);
	
	declare @Properties as table (
		PropertyIndex       integer not null,
		Value               nvarchar(3750) not null,
		primary key (PropertyIndex)
	);
	
	begin try;
		
		begin transaction;
		
		-- Search for entity.
		execute VanguardDocumentation.SelectEntity @EntityName, @EntityType,
			@Level0Name out, @Level0Type out, @Level1Name out, @Level1Type out, @Level2Name out, @Level2Type out;
		
		-- Search for properties.
		insert into @Properties
		select substring(name, len(@PropertyName) + 2, 8), convert(nvarchar(3750), value)
		from fn_listextendedproperty
			(null, @Level0Type, @Level0Name, @Level1Type, @Level1Name, @Level2Type, @Level2Name)
		where name = @PropertyName
			or name like (@PropertyName + '\_[0-9]%') escape '\'
			and name not like (@PropertyName + '\_[0-9]%[^0-9]%') escape '\';
		
		set @Text = (
			select Value as [text()]
			from @Properties
			order by PropertyIndex
			for xml path ('')
		);
		
		commit transaction;
		
	end try
	begin catch;
		
		set @ErrorNo = error_number();
		set @ErrorMsg = left(error_message(), 1536);
		set @ErrorLine = error_line();
		set @ErrorState = error_state();
		
		if @@trancount > 0
			rollback transaction;
		
		raiserror (740900, 16, @ErrorState, 'GetTextProperty', @ErrorNo, @ErrorLine, @ErrorMsg);
		
	end catch;
	
end;
GO




